Welcome to the next section during this section we're going to be talking about a very powerful tool
and add in within Microsoft Excel called Power pivot.
Now before we jump in and start to utilize this tool and some of its functionality I want to talk about
the why why should you be interested in Excel.
Power pivot and what is it.
This tool is Adun going to do for you.
So let's talk about this first Excel power pivots.
The idea behind Excel power pivot is we get to create what we're going to refer to as data models data
models are essentially our data.
It's a list.
It's really simply just that it's a list but through a data model we can set up multiple lists.
Take a look at the example file that I have open in front of you.
This is the same one we've been using Excel one or two exercises and inside here I've got two worksheets
that I want to direct your attention to.
First one is customer ampho.
The second one right next door is called Order info.
Now these two datasets are actually coming from an Access database.
This is based on the Northwind sample database that gets shipped with Microsoft Access.
I just imported the data directly into this Excel document.
Now back inside the database this is two separate tables.
Customer info and order info but they are related data.
There is some type of relationship between these two sets of data.
For example I've got customers and inside here my first customer Alph key the customer id their Alfreds
and all the contact information for that customer relates to multiple orders found inside of this list
here.
If I were to go up to the customer id call the and set of order info and sort by this I'll just do a
quick little ace Indian sort we can see that there are now many as at 6 records related back to that
alky customer which is back and said the customer info worksheet so there is a relationship between
these two bits of data customers and those customers have orders.
Now I want to be able to report off of this data but this is two separate bits of data.
Well you know what true power pivots we can create a data model that holds both of these lists customer
info and order info and we can create a relationship between these two sets of data because both customer
ampho and order info contain a customer ID.
We have a common value that we can relate through.
Now why is this important.
Well take a look.
What if I jump over to the customer info list I click into that list and I want to create a Pivot Table
.
So I go up to my insert town I go to create a Pivot Table.
All right I'm going to go ahead and grab that range is my customer info I'm going to create a new worksheet
.
OK.
Now I've got a new worksheet here but I've only got the customer info data a part of this pivot table
.
I need the order info as well because I want to create a report based off of all of that information
.
Well Double-O there's an option for more tables.
That sounds useful.
There's another table.
One is called Order info.
So I'll get that by Nikolic more tables.
I'll create a new pivot table.
Do you want to create a new pivot table.
No not really.
And I want to create a new pivot table that is what one pivot table that holds multiple sources.
Do you use multiple tables in your analysis a new pivot table in these be created using the data model
.
So I got to create a data model first which holds both of these sets of data create the relationship
between them and then create the pivot table.
This is why this is so important and so powerful we can now take multiple sets of data whether they
live inside of Excel or outside.
We can reach out to other databases or online services and input that data into a data model with in
our Excel document and then report off of that data and create relationships between multiple sets of
data.
So let's try this out.
Let's see how the Excel power pivot table works inside of this course here.
Let's try it out